package br.ita.comp.ces22.quiz.core.dao.jdbc;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.ita.comp.ces22.quiz.core.ConnectionManager;
import br.ita.comp.ces22.quiz.core.dao.QuizDAO;
import br.ita.comp.ces22.quiz.domain.Question;
import br.ita.comp.ces22.quiz.domain.Quiz;
import br.ita.comp.ces22.quiz.domain.QuizKey;
import br.ita.comp.ces22.quiz.domain.User;

public class QuizJDBCDAO extends QuizDAO{

	public Quiz getById(Object oQuizKey) {
		PreparedStatement stm = null;
		ResultSet rs = null;
		try {
			QuizKey quizKey = (QuizKey) oQuizKey;
			String query = "SELECT * FROM quiz WHERE idquestion=? and login=?";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setInt(1, quizKey.getIdQuestion());
			stm.setString(2, quizKey.getLogin());
			rs = stm.executeQuery();
			if (rs.next()) {
				Quiz quiz = new Quiz();
				QuizKey qk = new QuizKey();
				qk.setIdQuestion(new Integer(rs.getInt("idquestion")));
				qk.setLogin(rs.getString("login"));
				quiz.setQk(qk);
				quiz.setHit(rs.getInt("hit"));
				return quiz;
			}
			return null;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void insert(Quiz quiz){
		PreparedStatement stm = null;
		try {
			String query = "INSERT INTO quiz (login, idquestion, hit) VALUES (?, ?, ?)";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);

			stm.setString(1, quiz.getQk().getLogin());
			stm.setInt(2, quiz.getQk().getIdQuestion().intValue());
			stm.setInt(3, quiz.getHit().intValue());
			stm.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List<Question> getRandomQuestions(User user){
		PreparedStatement stm = null;
		ResultSet rs = null;
		try {
			String query = "select q.idquestion, q.question, q.alternative1, "
				+ "q.alternative2, q.alternative3, q.alternative4, q.alternative5 "
				+ "FROM question q WHERE q.idquestion NOT IN(SELECT qu.idquestion "
				+ "FROM user u JOIN quiz qz ON u.login = qz.login "
				+ "JOIN question qu ON qz.idquestion = qu.idquestion "
				+ "WHERE u.login = ?)";
			
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setString(1, user.getLogin());

			rs = stm.executeQuery();
			ArrayList<Question> questionList = new ArrayList<Question>();
			while (rs.next()){
				Question question = new Question();
				question.setAlternative1(rs.getString("alternative1"));
				question.setAlternative2(rs.getString("alternative2"));
				question.setAlternative3(rs.getString("alternative3"));
				question.setAlternative4(rs.getString("alternative4"));
				question.setAlternative5(rs.getString("alternative5"));
				question.setQuestion(rs.getString("question"));
				question.setIdQuestion(rs.getInt("idquestion"));

				questionList.add(question);
			}
			
			ArrayList<Question> randomList = new ArrayList<Question>();
			
			int count =0;
			while(count <5){
				if(questionList.size()>0){
					int i = (int) (questionList.size()*Math.random());
					randomList.add(questionList.get(i));
					questionList.remove(i);
				}
				count++;
			}
			return randomList;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}